package com.xmy.cultivate.mapper;

import com.xmy.cultivate.entity.OrderDisClan;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 真系亲属表 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2024-09-30
 */
public interface OrderDisClanMapper extends BaseMapper<OrderDisClan> {

    /**
     * 根据学生id获取其他亲属
     */
    @Select({"<script>",
            "WITH friend_student_from_with AS (",
                "SELECT",
                "* ",
                "FROM",
                "(",
                    "SELECT",
                    "t1.student_id AS student_id_from,",
                    "t2.student_id AS student_id_to ",
                    "FROM",
                    "order_apply AS t1",
                    "INNER JOIN order_dis_clan AS t2 ON t1.id = t2.order_id ",
                ") AS t01 ",
                "GROUP BY",
                "student_id_from,",
                "student_id_to ",
            "),",
            "friend_student_to_with AS (",
                "SELECT",
                "* ",
                "FROM",
                "(",
                    "SELECT",
                    "t2.student_id AS student_id_to,",
                    "t1.student_id AS student_id_from ",
                    "FROM",
                    "order_apply AS t1",
                    "INNER JOIN order_dis_clan AS t2 ON t1.id = t2.order_id ",
                ") AS t01 ",
                "GROUP BY",
                "student_id_from,",
                "student_id_to ",
            "),",
            "frined_student_all_whith AS ( SELECT student_id_from, student_id_to FROM friend_student_from_with UNION SELECT student_id_to AS student_id_from, student_id_from AS student_id_to FROM friend_student_to_with ) SELECT",
            "student_id_from,",
            "student_id_to,",
            "getStudentNameForId ( student_id_from ) AS student_name_from,",
            "getStudentNameForId ( student_id_to ) AS student_name_to ",
            "FROM",
            "frined_student_all_whith ",
            "WHERE",
            "student_id_from = ${studentId};",
            "</script>"})
    public abstract List<OrderDisClan> getOrderDisClanByStudentId(@Param("studentId") Long studentId);

}
